<?php

class model_dxmOrder extends L {

    /**
     * 描述 ：分页的查询语句拼接
     * 作者 ：Zoro.Zhu
    **/
    public static function searchSql($params = array(), $type = 'list'){
        if($type === 'list'){
            $title = '`orderId`, `orderState`, `dxmAccount`, `pullTime`, `updTime`, 
             `orderData`, `wishUData`, `aliData`, `profitsData`, `singleExpress`,
             `costPriceTotal`, CONVERT(profitsData->>\'$.costMargin\', DECIMAL(10,2)) as costMargin,
             CONVERT(profitsData->>\'$.grossMargin\', DECIMAL(10,2)) as grossMargin,
             CONVERT(profitsData->>\'$.gross\', DECIMAL(10,2)) as gross,
             CONVERT(profitsData->>\'$.refundPrice\', DECIMAL(10,2)) as refundPrice,
             `trackData`, CONVERT(`orderData`->>\'$.printTime\', DATETIME) as printTime,
             CONVERT(`orderData`->>\'$.paymentTime\', DATETIME) as paymentTime,
             CONVERT(`trackData`->>\'$.shippingDay\', SIGNED) as shippingDay,
             CONVERT(`trackData`->>\'$.lastTimeStr\', DATETIME) as lastTime,
             orderRemark, refund';
        } elseif ($type = 'total'){
            $title = 'SUM(`profitsData` ->> \'$.salesPrice\') AS salesPrice,
             SUM(`profitsData` ->> \'$.freightRevenue\') AS freightRevenue,
             SUM(`profitsData` ->> \'$.platPrice\') AS platPrice,
             SUM(`profitsData` ->> \'$.costPrice\') AS costPrice,
             SUM(`profitsData` ->> \'$.shipFee\') AS shipFee,
             SUM(`profitsData` ->> \'$.gross\') AS gross,
             SUM(`profitsData` ->> \'$.refundPrice\') AS refundPrice';
        }
        $sql = "SELECT
             {$title}
        FROM
            `dxm_order`
        WHERE 1";
        $dxmAccount = model_com::getDxmAccount();
        $sql .= " AND `dxmAccount` IN ('" . join("','", $dxmAccount) . "')";
        $sql .= " AND `orderState` != '已作废' ";
        $sql .= !empty($params['search']['dxmAccount']) && is_array($params['search']['dxmAccount']) ? " AND `dxmAccount` IN ( '" . join("','", $params['search']['dxmAccount']) . "') " : "";
        $sql .= !empty($params['search']['orderId']) ? " AND `orderId` = '{$params['search']['orderId']}' " : "";
        $sql .= !empty($params['search']['orderState']) && is_array($params['search']['orderState']) ? " AND `orderState` IN ( '" . join("','", $params['search']['orderState']) . "') " : "";
        $sql .= !empty($params['search']['trackDogStatus']) && is_array($params['search']['trackDogStatus'])? " AND `trackData`->>'$.status' IN ( '" . join("','", $params['search']['trackDogStatus']) . "') " : "";
        $sql .= !empty($params['search']['parcelNum']) ? " AND `orderData`->>'$.parcelNum' LIKE '%{$params['search']['parcelNum']}%' " : "";
        $sql .= !empty($params['search']['pAccount']) ? " AND `orderData`->>'$.pAccount' LIKE '%{$params['search']['pAccount']}%' " : "";
        $sql .= !empty($params['search']['country']) ? " AND `orderData`->>'$.country' LIKE '%{$params['search']['country']}%' " : "";
        $sql .= !empty($params['search']['logistics']) ? " AND `orderData`->>'$.logistics' LIKE '%{$params['search']['logistics']}%' " : "";
        $sql .= !empty($params['search']['trackNo']) ? " AND `orderData`->>'$.trackNo' LIKE '%{$params['search']['trackNo']}%' " : "";
        $sql .= !empty($params['search']['contact']) ? " AND `orderData`->>'$.contact' LIKE '%{$params['search']['contact']}%' " : "";
        $sql .= !empty($params['search']['sku']) ? " AND `orderData` LIKE '%{$params['search']['sku']}%' " : "";
        $sql .= !empty($params['search']['pickingRemark']) ? " AND `orderData`->>'$.pickingRemark' LIKE '%{$params['search']['pickingRemark']}%' " : "";
        $sql .= !empty($params['search']['singleExpress']) ? " AND `singleExpress` LIKE '%{$params['search']['singleExpress']}%' " : "";
        $sql .= !empty($params['search']['paymentTimeStart']) ? " AND `orderData`->>'$.paymentTime' >= '{$params['search']['paymentTimeStart']}' " : "";
        $sql .= !empty($params['search']['paymentTimeEnd']) ? " AND `orderData`->>'$.paymentTime' <= '{$params['search']['paymentTimeEnd']} 23:59:59' " : "";
        $sql .= !empty($params['search']['printTimeStart']) ? " AND `orderData`->>'$.printTime' >= '{$params['search']['printTimeStart']}' " : "";
        $sql .= !empty($params['search']['printTimeEnd']) ? " AND `orderData`->>'$.printTime' <= '{$params['search']['printTimeEnd']} 23:59:59' " : "";
        $sql .= !empty($params['search']['logisticsTimeStart']) ? " AND `trackData`->>'$.lastTimeStr' >= '{$params['search']['logisticsTimeStart']}' " : "";
        $sql .= !empty($params['search']['logisticsTimeEnd']) ? " AND `trackData`->>'$.lastTimeStr' <= '{$params['search']['logisticsTimeEnd']} 23:59:59' " : "";
        $sql .= isset($params['search']['grossStart']) && $params['search']['grossStart'] !== '' ? " AND CONVERT(`profitsData`->>'$.gross', DECIMAL(10,2)) >= '{$params['search']['grossStart']}' " : "";
        $sql .= isset($params['search']['grossEnd']) && $params['search']['grossEnd'] !== '' ? " AND CONVERT(`profitsData`->>'$.gross', DECIMAL(10,2)) <= '{$params['search']['grossEnd']}' " : "";
        $sql .= isset($params['search']['grossMarginStart']) && $params['search']['grossMarginStart'] !== '' ? " AND CONVERT(`profitsData`->>'$.grossMargin', DECIMAL(10,2)) >= '{$params['search']['grossMarginStart']}' " : "";
        $sql .= isset($params['search']['grossMarginEnd']) && $params['search']['grossMarginEnd'] !== '' ? " AND CONVERT(`profitsData`->>'$.grossMargin', DECIMAL(10,2)) <= '{$params['search']['grossMarginEnd']}' " : "";
        $sql .= isset($params['search']['costMarginStart']) && $params['search']['costMarginStart'] !== '' ? " AND CONVERT(`profitsData`->>'$.costMargin', DECIMAL(10,2)) >= '{$params['search']['costMarginStart']}' " : "";
        $sql .= isset($params['search']['costMarginEnd']) && $params['search']['costMarginEnd'] !== '' ? " AND CONVERT(`profitsData`->>'$.costMargin', DECIMAL(10,2)) <= '{$params['search']['costMarginEnd']}' " : "";
        $sql .= isset($params['search']['costPriceTotalStart']) && $params['search']['costPriceTotalStart'] !== '' ? " AND costPriceTotal >= '{$params['search']['costPriceTotalStart']}' " : "";
        $sql .= isset($params['search']['costPriceTotalEnd']) && $params['search']['costPriceTotalEnd'] !== '' ? " AND costPriceTotal <= '{$params['search']['costPriceTotalEnd']}' " : "";
        if($type === 'list'){
            isset($params['sort']) && $sql .= " ORDER BY {$params['sort']} ";
        }
        return $sql;
    }

    /**
    * 描述 ：分页的查询语句拼接
    * 作者 ：Zoro.Zhu
    **/
    public static function searchSqlEs($params = array(), $type = 'list'){
        if($type === 'list'){
            $title = 'orderId';
        } elseif ($type = 'total'){
            $title = 'SUM(salesPrice), SUM(freightRevenue), SUM(platPrice),
             SUM(costPrice), SUM(shipFee), SUM(gross), SUM(refundPrice)';
        }
        $sql = "SELECT
             {$title}
        FROM
            dxm_order
        WHERE ";
        $dxmAccount = model_com::getDxmAccount();
        $sql .= " dxmAccount IN ('" . join("','", $dxmAccount) . "')";
        $sql .= " AND orderState != '已作废' ";
        $sql .= !empty($params['search']['dxmAccount']) && is_array($params['search']['dxmAccount']) ? " AND `dxmAccount` IN ( '" . join("','", $params['search']['dxmAccount']) . "') " : "";
        $sql .= !empty($params['search']['orderId']) ? " AND `orderId` = '{$params['search']['orderId']}' " : "";
        $sql .= !empty($params['search']['orderState']) && is_array($params['search']['orderState']) ? " AND `orderState` IN ( '" . join("','", $params['search']['orderState']) . "') " : "";
        $sql .= !empty($params['search']['trackDogStatus']) && is_array($params['search']['trackDogStatus'])? " AND trackDogStatus IN ( '" . join("','", $params['search']['trackDogStatus']) . "') " : "";
        $sql .= !empty($params['search']['parcelNum']) ? " AND parcelNum LIKE '%{$params['search']['parcelNum']}%' " : "";
        $sql .= !empty($params['search']['pAccount']) ? " AND pAccount LIKE '%{$params['search']['pAccount']}%' " : "";
        $sql .= !empty($params['search']['country']) ? " AND country LIKE '%{$params['search']['country']}%' " : "";
        $sql .= !empty($params['search']['logistics']) ? " AND logistics LIKE '%{$params['search']['logistics']}%' " : "";
        $sql .= !empty($params['search']['trackNo']) ? " AND trackNo LIKE '%{$params['search']['trackNo']}%' " : "";
        $sql .= !empty($params['search']['contact']) ? " AND contact LIKE '%{$params['search']['contact']}%' " : "";
        $sql .= !empty($params['search']['sku']) ? " AND `sku` LIKE '%{$params['search']['sku']}%' " : "";
        $sql .= !empty($params['search']['pickingRemark']) ? " AND pickingRemark LIKE '%{$params['search']['pickingRemark']}%' " : "";
        $sql .= !empty($params['search']['singleExpress']) ? " AND `singleExpress` LIKE '%{$params['search']['singleExpress']}%' " : "";
        $sql .= !empty($params['search']['paymentTimeStart']) ? " AND paymentTime >= '{$params['search']['paymentTimeStart']}' " : "";
        $sql .= !empty($params['search']['paymentTimeEnd']) ? " AND paymentTime <= '{$params['search']['paymentTimeEnd']} 23:59:59' " : "";
        $sql .= !empty($params['search']['printTimeStart']) ? " AND printTime >= '{$params['search']['printTimeStart']}' " : "";
        $sql .= !empty($params['search']['printTimeEnd']) ? " AND printTime <= '{$params['search']['printTimeEnd']} 23:59:59' " : "";
        $sql .= !empty($params['search']['logisticsTimeStart']) ? " AND lastTimeStr >= '{$params['search']['logisticsTimeStart']}' " : "";
        $sql .= !empty($params['search']['logisticsTimeEnd']) ? " AND lastTimeStr <= '{$params['search']['logisticsTimeEnd']} 23:59:59' " : "";
        $sql .= isset($params['search']['grossStart']) && $params['search']['grossStart'] !== '' ? " AND gross >= '{$params['search']['grossStart']}' " : "";
        $sql .= isset($params['search']['grossEnd']) && $params['search']['grossEnd'] !== '' ? " AND gross <= '{$params['search']['grossEnd']}' " : "";
        $sql .= isset($params['search']['grossMarginStart']) && $params['search']['grossMarginStart'] !== '' ? " AND grossMargin >= '{$params['search']['grossMarginStart']}' " : "";
        $sql .= isset($params['search']['grossMarginEnd']) && $params['search']['grossMarginEnd'] !== '' ? " AND grossMargin <= '{$params['search']['grossMarginEnd']}' " : "";
        $sql .= isset($params['search']['costMarginStart']) && $params['search']['costMarginStart'] !== '' ? " AND costMargin >= '{$params['search']['costMarginStart']}' " : "";
        $sql .= isset($params['search']['costMarginEnd']) && $params['search']['costMarginEnd'] !== '' ? " AND costMargin <= '{$params['search']['costMarginEnd']}' " : "";
        $sql .= isset($params['search']['costPriceTotalStart']) && $params['search']['costPriceTotalStart'] !== '' ? " AND costPriceTotal >= '{$params['search']['costPriceTotalStart']}' " : "";
        $sql .= isset($params['search']['costPriceTotalEnd']) && $params['search']['costPriceTotalEnd'] !== '' ? " AND costPriceTotal <= '{$params['search']['costPriceTotalEnd']}' " : "";

        if($type === 'list'){
            isset($params['sort']) && $sql .= " ORDER BY {$params['sort']} ";
        }
        return $sql;
    }

    /**
     * 描述 : 获取订单数据
     * 作者 : Zoro.Zhu
     */
    public static function getOrderInfo($orderId = []){
        if(empty($orderId)){
            return [];
        }
        $sql = "SELECT
             `orderId`, `orderState`, `dxmAccount`, `pullTime`, `updTime`, 
             `orderData`, `wishUData`, `aliData`, `profitsData`, `singleExpress`,
             `costPriceTotal`, CONVERT(profitsData->>'$.costMargin', DECIMAL(10,2)) as costMargin,
             CONVERT(profitsData->>'$.grossMargin', DECIMAL(10,2)) as grossMargin,
             CONVERT(profitsData->>'$.gross', DECIMAL(10,2)) as gross,
             `trackData`, CONVERT(`orderData`->>'$.printTime', DATETIME) as printTime,
             CONVERT(`orderData`->>'$.paymentTime', DATETIME) as paymentTime,
             CONVERT(`trackData`->>'$.shippingDay', SIGNED) as shippingDay,
             CONVERT(`trackData`->>'$.lastTimeStr', DATETIME) as lastTime,
             orderRemark
        FROM
            `dxm_order`
        WHERE orderId IN ('" . join("','", $orderId) . "')
        ORDER BY FIELD(orderId,'" . join("','", $orderId) . "')";
        return L::sql($sql);
    }

    /**
     * 描述 : 获取搜索条件下的总金额
     * 作者 : Zoro.Zhu
     */
    public static function getPriceTotal($params){
        $sql = "SELECT
             SUM(`profitsData` ->> '$.salesPrice') AS salesPrice,
             SUM(`profitsData` ->> '$.freightRevenue') AS freightRevenue,
             SUM(`profitsData` ->> '$.platPrice') AS platPrice,
             SUM(`profitsData` ->> '$.costPrice') AS costPrice,
             SUM(`profitsData` ->> '$.shipFee') AS shipFee,
             SUM(`profitsData` ->> '$.gross') AS gross,
             SUM(`profitsData` ->> '$.refundPrice') AS refundPrice
        FROM
            `dxm_order`
        WHERE 1";

        $dxmAccount = model_com::getDxmAccount();
        $sql .= " AND `dxmAccount` IN ('" . join("','", $dxmAccount) . "')";
        $sql .= " AND `orderState` != '已作废' ";
        $sql .= !empty($params['search']['orderId']) ? " AND `orderId` = '{$params['search']['orderId']}' " : "";
        $sql .= !empty($params['search']['orderState']) ? " AND `orderState` = '{$params['search']['orderState']}' " : "";
        $sql .= !empty($params['search']['trackDogStatus']) ? " AND `trackData`->>'$.status' = '{$params['search']['trackDogStatus']}' " : "";
        $sql .= !empty($params['search']['parcelNum']) ? " AND `orderData`->>'$.parcelNum' LIKE '%{$params['search']['parcelNum']}%' " : "";
        $sql .= !empty($params['search']['pAccount']) ? " AND `orderData`->>'$.pAccount' LIKE '%{$params['search']['pAccount']}%' " : "";
        $sql .= !empty($params['search']['country']) ? " AND `orderData`->>'$.country' LIKE '%{$params['search']['country']}%' " : "";
        $sql .= !empty($params['search']['logistics']) ? " AND `orderData`->>'$.logistics' LIKE '%{$params['search']['logistics']}%' " : "";
        $sql .= !empty($params['search']['trackNo']) ? " AND `orderData`->>'$.trackNo' LIKE '%{$params['search']['trackNo']}%' " : "";
        $sql .= !empty($params['search']['contact']) ? " AND `orderData`->>'$.contact' LIKE '%{$params['search']['contact']}%' " : "";
        $sql .= !empty($params['search']['sku']) ? " AND `orderData` LIKE '%{$params['search']['sku']}%' " : "";
        $sql .= !empty($params['search']['pickingRemark']) ? " AND `orderData`->>'$.pickingRemark' LIKE '%{$params['search']['pickingRemark']}%' " : "";
        $sql .= !empty($params['search']['singleExpress']) ? " AND `singleExpress` LIKE '%{$params['search']['singleExpress']}%' " : "";
        $sql .= !empty($params['search']['paymentTimeStart']) ? " AND `orderData`->>'$.paymentTime' >= '{$params['search']['paymentTimeStart']}' " : "";
        $sql .= !empty($params['search']['paymentTimeEnd']) ? " AND `orderData`->>'$.paymentTime' <= '{$params['search']['paymentTimeEnd']} 23:59:59' " : "";
        $sql .= !empty($params['search']['printTimeStart']) ? " AND `orderData`->>'$.printTime' >= '{$params['search']['printTimeStart']}' " : "";
        $sql .= !empty($params['search']['printTimeEnd']) ? " AND `orderData`->>'$.printTime' <= '{$params['search']['printTimeEnd']} 23:59:59' " : "";
        $sql .= !empty($params['search']['logisticsTimeStart']) ? " AND `trackData`->>'$.lastTimeStr' >= '{$params['search']['logisticsTimeStart']}' " : "";
        $sql .= !empty($params['search']['logisticsTimeEnd']) ? " AND `trackData`->>'$.lastTimeStr' <= '{$params['search']['logisticsTimeEnd']} 23:59:59' " : "";
        $sql .= isset($params['search']['grossStart']) && $params['search']['grossStart'] !== '' ? " AND CONVERT(`profitsData`->>'$.gross', DECIMAL(10,2)) >= '{$params['search']['grossStart']}' " : "";
        $sql .= isset($params['search']['grossEnd']) && $params['search']['grossEnd'] !== '' ? " AND CONVERT(`profitsData`->>'$.gross', DECIMAL(10,2)) <= '{$params['search']['grossEnd']}' " : "";
        $sql .= isset($params['search']['grossMarginStart']) && $params['search']['grossMarginStart'] !== '' ? " AND CONVERT(`profitsData`->>'$.grossMargin', DECIMAL(10,2)) >= '{$params['search']['grossMarginStart']}' " : "";
        $sql .= isset($params['search']['grossMarginEnd']) && $params['search']['grossMarginEnd'] !== '' ? " AND CONVERT(`profitsData`->>'$.grossMargin', DECIMAL(10,2)) <= '{$params['search']['grossMarginEnd']}' " : "";
        $sql .= isset($params['search']['costMarginStart']) && $params['search']['costMarginStart'] !== '' ? " AND CONVERT(`profitsData`->>'$.costMargin', DECIMAL(10,2)) >= '{$params['search']['costMarginStart']}' " : "";
        $sql .= isset($params['search']['costMarginEnd']) && $params['search']['costMarginEnd'] !== '' ? " AND CONVERT(`profitsData`->>'$.costMargin', DECIMAL(10,2)) <= '{$params['search']['costMarginEnd']}' " : "";
        $sql .= isset($params['search']['costPriceTotalStart']) && $params['search']['costPriceTotalStart'] !== '' ? " AND costPriceTotal >= '{$params['search']['costPriceTotalStart']}' " : "";
        $sql .= isset($params['search']['costPriceTotalEnd']) && $params['search']['costPriceTotalEnd'] !== '' ? " AND costPriceTotal <= '{$params['search']['costPriceTotalEnd']}' " : "";

        return $sql;
        $res = L::sql($sql);
        $data = [];
        foreach ($res[0] as $key => $val){
            $data[$key] = round($val, 2);
        }
        return $data;
    }

    /**
     * 描述 ：执行修改数据的sql
     * 作者 ：Zoro.Zhu
    **/
    public static function updateSql($data = array()){
        $sql = "UPDATE `dxm_order` 
        SET 
            `orderState` = '{$data['orderState']}',
            `dxmAccount` = '{$data['dxmAccount']}',
            `pullTime` = '{$data['pullTime']}',
            `updTime` = '{$data['updTime']}',
            `orderData` = '{$data['orderData']}',
            `wishUData` = '{$data['wishUData']}',
            `aliData` = '{$data['aliData']}',
            `profitsData` = '{$data['profitsData']}'
        WHERE
            `orderId` = '{$data['orderId']}'";
        return L::sql($sql);
    }

    /**
     * 描述 ：执行插入数据的sql
     * 作者 ：Zoro.Zhu
    **/
    public static function insertSql($data = array()){
        $sql = "INSERT INTO `dxm_order`(
             `orderState`, `dxmAccount`, `pullTime`, `updTime`, `orderData`, `wishUData`, `aliData`, `profitsData`
        ) VALUES (
             '{$data['orderState']}', '{$data['dxmAccount']}', '{$data['pullTime']}', '{$data['updTime']}', '{$data['orderData']}', '{$data['wishUData']}', '{$data['aliData']}', '{$data['profitsData']}'
        )";
        return L::sql($sql);
    }

    /**
     * 描述：根据主键获取数据
     * 作者：Zoro.Zhu
    **/
    public static function getKeyInfo($key){
        $sql = "SELECT
             `orderId`, `orderState`, `dxmAccount`, `pullTime`, `updTime`, 
             `orderData`, `wishUData`, `aliData`, `profitsData`, `singleExpress`,
             `costPriceTotal`, `orderRemark`
        FROM
            `dxm_order`
        WHERE 
            `orderId` = '{$key}'";
        return L::sql($sql);
    }

    /**
     * 描述 : 获取渠道列表
     * 作者 : Zoro.Zhu
     */
    public static function getLogisticsList(){
        $sql = "SELECT
            dxmAccount, logisticsList 
        FROM
            `dxm_logistics` 
        WHERE
            1";
        $dxmAccount = model_com::getDxmAccount();
        $sql .= " AND `dxmAccount` IN ('" . join("','", $dxmAccount) . "')";
        $info = L::sql($sql);
        $data = [];
        foreach($info as $row){
            $list = json_decode($row['logisticsList'], true);
            foreach($list['all'] as $r){
                $data[$r['agentProviderName']] = $r['id'];
            }
        }
        return $data;
    }

}